﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;

namespace SwormTests.Joins
{
    public class simple_one_to_one : TestBase
    {
        public simple_one_to_one(DbToTest type) : base(type) { }

        public class my_foreign
        {
            public string Id { get; set; }
            public string Table { get; set; }
            public string PrimaryId { get; set; }
            public my_primary Primary { get; set; }
        }

        public class my_primary
        {
            public string Id { get; set; }
            public string Table { get; set; }
            public my_foreign Foreign { get; set; }
        }

        protected override void Initialize()
        {
            DB.Insert(new my_primary() { Id = "p1", Table = "primary '1" });
            DB.Insert(new my_primary() { Id = "p2", Table = "primary '2" });
            DB.Insert(new my_foreign() { Id = "f1", Table = "foreign '1", PrimaryId = "p1" });
            DB.Insert(new my_foreign() { Id = "f3", Table = "foreign '3" });
        }

        [Test]
        public void can_outer_join_from_fk_side()
        {
            var items = DB.Query.Joining<my_foreign>("f")
                .JoinLeft(f => f.Primary, "p")
                .List();

            Assert.AreEqual(2, items.Count);

            var f1 = items.Single(i => i.Id == "f1");
            var f3 = items.Single(i => i.Id == "f3");

            Assert.IsNotNull(f1.Primary);
            Assert.AreEqual("p1", f1.PrimaryId);
            Assert.AreEqual("primary '1", f1.Primary.Table);

            Assert.IsNull(f3.Primary);
            Assert.IsNull(f3.PrimaryId);
        }

        [Test]
        public void can_inner_join_from_fk_side()
        {
            var items = DB.Query.Joining<my_foreign>("f")
                .JoinInner(f => f.Primary, "p")
                .List();

            Assert.AreEqual(1, items.Count);

            var f1 = items.Single(i => i.Id == "f1");

            Assert.IsNotNull(f1.Primary);
            Assert.AreEqual("p1", f1.PrimaryId);
            Assert.AreEqual("primary '1", f1.Primary.Table);
        }

        [Test]
        public void can_outer_join_from_primary_side()
        {
            var items = DB.Query.Joining<my_primary>("p")
                .JoinLeft(p => p.Foreign, "f")
                .List();

            Assert.AreEqual(2, items.Count);

            var p1 = items.Single(p => p.Id == "p1");
            var p2 = items.Single(p => p.Id == "p2");

            Assert.IsNotNull(p1.Foreign);
            Assert.AreEqual("f1", p1.Foreign.Id);
            Assert.AreEqual("foreign '1", p1.Foreign.Table);

            Assert.IsNull(p2.Foreign);
        }

        [Test]
        public void can_inner_join_from_primary_side()
        {
            var items = DB.Query.Joining<my_primary>("p")
                .JoinInner(p => p.Foreign, "f")
                .List();

            Assert.AreEqual(1, items.Count);

            var p1 = items.Single(p => p.Id == "p1");
            Assert.IsNotNull(p1.Foreign);
            Assert.AreEqual("f1", p1.Foreign.Id);
            Assert.AreEqual("foreign '1", p1.Foreign.Table);
        }

        [Test]
        public void with_where_and_orderby()
        {
            DB.Execute(@"delete from my_primary");
            DB.Execute(@"delete from my_foreign");

            DB.Insert(new my_primary() { Id = "p1", Table = "zz" });
            DB.Insert(new my_primary() { Id = "p2", Table = "zy" });
            DB.Insert(new my_primary() { Id = "p3", Table = "xx" });
            DB.Insert(new my_foreign() { Id = "f1", PrimaryId = "p2", Table = "aa" });
            DB.Insert(new my_foreign() { Id = "f2", PrimaryId = "p3", Table = "cc" });
            DB.Insert(new my_foreign() { Id = "f3", PrimaryId = "p1", Table = "bb" });

            var items = DB.Query.Joining<my_primary>("p")
                .JoinInner(p => p.Foreign, "f")
                .Where(@"p.""Table"" like @0", "z%")
                .OrderBy(@"f.""Id"" asc")
                .List();

            Assert.AreEqual(2, items.Count);

            Assert.AreEqual("f1", items[0].Foreign.Id);
            Assert.AreEqual("p2", items[0].Id);
            Assert.AreEqual("zy", items[0].Table);

            Assert.AreEqual("f3", items[1].Foreign.Id);
            Assert.AreEqual("p1", items[1].Id);
            Assert.AreEqual("zz", items[1].Table);

            // change orderby to desc
            items = DB.Query.Joining<my_primary>("p")
                .JoinInner(p => p.Foreign, "f")
                .Where(@"p.""Table"" like @0", "z%")
                .OrderBy(@"f.""Id"" desc")
                .List();

            Assert.AreEqual(2, items.Count);

            Assert.AreEqual("f1", items[1].Foreign.Id);
            Assert.AreEqual("p2", items[1].Id);
            Assert.AreEqual("zy", items[1].Table);

            Assert.AreEqual("f3", items[0].Foreign.Id);
            Assert.AreEqual("p1", items[0].Id);
            Assert.AreEqual("zz", items[0].Table);
        }
    }
}
